﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class SelfBreakDown : BasicSelfService, ISelfBreakDown
    {
        private readonly static string connstr = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
        private const string sqlInsert = "insert into breakdown (lvlname,parentid) values(:lvlname,:parentid)";
        private const string sqlDelete = "delete from breakdown where id=:id";
        private const string sqlGetBreakDwonbyName = "select * from breakdown where lvlname=:lvlname";
        private const string sqlupdate = "update breakdown set lvlname=:lvlname where ID=:id";
        private const string sqlgetallBreakDwon = "select * from breakdown";
        private const string sqlbdCategory = "select * from breakdown where parentid is null";


        public List<SelfBreakDwonModel> GetAllBreakDwon()
        {
            return ModelHelper<SelfBreakDwonModel>.GetModel_By_DirectSql(sqlgetallBreakDwon, null, oda);
        }

        public List<SelfBreakDwonModel> GetCategory()
        {
            return ModelHelper<SelfBreakDwonModel>.GetModel_By_DirectSql(sqlbdCategory, null, oda);
        }

        public SelfBreakDwonModel GetSelfBreakDwonByName(string name)
        {
            SelfBreakDwonModel model = new SelfBreakDwonModel();

            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                OracleCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = @"select * from breakdown where lvlname=:lvlname";
                var name1 = new OracleParameter("lvlname", OracleDbType.NVarchar2, 100);
                name1.Value = name;

                com.Parameters.Add(name1);

                using (var reader = com.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader["ID"] != DBNull.Value) model.ID = (decimal)reader["ID"];
                        if (reader["LvlName"] != DBNull.Value) model.LvlName = (string)reader["LvlName"];
                        if (reader["ParentID"] != DBNull.Value) model.ParentID = (decimal)reader["ParentID"];
                    }
                }
            }
            return model;
        }

        public SelfBreakDwonModel GetSelfBreakDwonByID(int id)
        {
            SelfBreakDwonModel model = new SelfBreakDwonModel();

            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                OracleCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = @"select * from breakdown where id=:id";
                var id1 = new OracleParameter("id", OracleDbType.Decimal, 100);
                id1.Value = id;

                com.Parameters.Add(id1);

                using (var reader = com.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader["ID"] != DBNull.Value) model.ID = (decimal)reader["ID"];
                        if (reader["LvlName"] != DBNull.Value) model.LvlName = (string)reader["LvlName"];
                        if (reader["ParentID"] != DBNull.Value) model.ParentID = (decimal)reader["ParentID"];
                    }
                }
            }
            return model;
        }


        public int Add(SelfBreakDwonModel model)
        {
            int result = 0;
            try
            {
                using (OracleConnection conn = new OracleConnection(connstr))
                {
                    conn.Open();
                    OracleCommand com = new OracleCommand();
                    this.PrepareCommand(com, conn, null, CommandType.Text, sqlInsert, new List<OracleParameter>() 
                        { 
                            new OracleParameter("LvlName", OracleDbType.NVarchar2, 100, model.LvlName, ParameterDirection.Input),
                            new OracleParameter("ParentID", OracleDbType.Decimal, 100, model.ParentID, ParameterDirection.Input)
                        });
                    com.ExecuteNonQuery();
                }
                result = 1;
            }
            catch (Exception e)
            {
                result = 0;
            }
            return result;
        }

        public void Delete(int ID)
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(connstr))
                {
                    conn.Open();
                    OracleCommand com = new OracleCommand();
                    this.PrepareCommand(com, conn, null, CommandType.Text, sqlDelete, new List<OracleParameter>() 
                        { 
                            new OracleParameter("id", OracleDbType.Decimal, 100, ID, ParameterDirection.Input)
                        });
                    com.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
            }
        }

        public int Update(SelfBreakDwonModel model)
        {
            int result = 0;
            if (model != null)
            {
                try
                {
                    using (OracleConnection conn = new OracleConnection(connstr))
                    {
                        conn.Open();
                        OracleCommand com = new OracleCommand();
                        this.PrepareCommand(com, conn, null, CommandType.Text, sqlupdate, new List<OracleParameter>() 
                        { 
                            new OracleParameter("ID", OracleDbType.Decimal, 100, model.ID, ParameterDirection.Input),
                            new OracleParameter("LvlName", OracleDbType.NVarchar2, 100, model.LvlName, ParameterDirection.Input)
                        });
                        com.ExecuteNonQuery();
                    }
                    result = 1;
                }
                catch (Exception e)
                {
                    result = 0;
                }
            }
            return result;
        }

        public List<SelfBreakDwonModel> GetExistBreakDwon(string lvlName)
        {
            return ModelHelper<SelfBreakDwonModel>.GetModel_By_DirectSql(sqlGetBreakDwonbyName, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="lvlName",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=50,Value=lvlName}
                 },
          oda);
        }

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, IList<OracleParameter> commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;

            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        var value = parm.Value;
                        if (value.GetType() == typeof(string) || parm.OracleDbType == OracleDbType.NVarchar2)
                        {
                            if (value != DBNull.Value)
                            {
                                parm.Size = ((string)value).Length > 4000 ? -1 : 4000;
                            }
                            else
                            {
                                parm.Size = 0;
                            }
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }

    }
}
